﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RealtorsPortal.Data_Access_Layer;
using System.Data.SqlClient;
using System.Data;
using RealtorsPortal.Entities;

namespace RealtorsPortal.Business_Logic_Layer
{
    public class CountriesDAL
    {
        DataAccessHelper daAccHelp = new DataAccessHelper();

        public DataTable getCountriesData()
        {
            DataTable dt = new DataTable();
            dt = daAccHelp.GetDataTable("sp_Get_Countries");
            return dt;
        }

        public int InsertCountrys(Country countryObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryCountrysToInsert(countryObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Insert_Countries", pList);
        }

        public int UpdateCountrys(Country countryObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryCountrysToUpdate(countryObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Update_Countries", pList);
        }

        public int DeleteCountrys(Country countryObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryCountrysToDelete(countryObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Delete_Countries", pList);
        }

        public DataTable GetCountriesByCountryName(Country countryObj) {
            DataTable dt = new DataTable();
            List<SqlParameter> pList = ExecuteNonQueryCountrysToInsert(countryObj);
            dt = daAccHelp.GetData("sp_Get_By_CountryName", pList);
            return dt;
        }

        public DataTable GetAllByCountryName(Country countryObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> pList = ExecuteNonQueryCountrysToInsert(countryObj);
            dt = daAccHelp.GetData("sp_Get_All_By_Countries", pList);
            return dt;
        }

        public DataTable GetCountriesById(Country countryObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> pList = ExecuteNonQueryCountrysToDelete(countryObj);
            dt = daAccHelp.GetData("sp_Get_Countries_By_Id", pList);
            return dt;
        }

        private List<SqlParameter> ExecuteNonQueryCountrysToInsert(Country countryObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            
            para = new SqlParameter("@CountryName", SqlDbType.NVarChar);
            para.Value = countryObj.CountryName;
            paraList.Add(para);

            return paraList;
        }
        
        private List<SqlParameter> ExecuteNonQueryCountrysToDelete(Country countryObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@CountryId", SqlDbType.NVarChar);
            para.Value = countryObj.CountryId;
            paraList.Add(para);
            
            return paraList;
        }

        private List<SqlParameter> ExecuteNonQueryCountrysToUpdate(Country countryObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@CountryId", SqlDbType.NVarChar);
            para.Value = countryObj.CountryId;
            paraList.Add(para);
            para = new SqlParameter("@CountryName", SqlDbType.NVarChar);
            para.Value = countryObj.CountryName;
            paraList.Add(para);

            return paraList;
        }
    }
}